package com.lanju.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.lanju.entity.Collect;
import com.lanju.entity.Server;
import com.lanju.entity.Service_Order_Detail;
import com.lanju.entity.Service_User_Rel;


@Repository
@Transactional
public class ServiceDao extends BaseDao<Server,String>{
    
    private static final Log log = LogFactory.getLog(ServiceDao.class);
   
    @Autowired
   	SessionFactory sessionFactory;
    
    @PersistenceContext(unitName = "financialmarketingUnit")
    private EntityManager entityManager;

    @SuppressWarnings("unchecked")
	public List<Map> queryServiceList(Map<String, Object> param, Integer pageSize, Integer offset) {
    	
		   try {
			   StringBuffer sql=new StringBuffer();
			   sql.append(" select a.*,b.imgUrl,c.price from (select * from  lj_service where status = '1' and serviceType = '"+param.get("serviceType")+"') a ");
			   sql.append(" left join  lj_service_img_rel b on a.serviceId = b.serviceId left join lj_service_price_rel c on a.serviceId = c.serviceId where b.type = '1'" ); 
			   sql.append(" order by a.createTime desc ");
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setFirstResult(offset)
				        .setMaxResults(pageSize).list();
	        } catch (Exception e) {
	            log.error("queryServiceList fail",e);
	        }
		   return null;
	}
    
	public long countServiceList(Map<String, Object> param) {
		 try {
			 StringBuffer sql=new StringBuffer();
			   sql.append(" select count(*) from (select * from  lj_service where status = '1' and serviceType = '"+param.get("serviceType")+"') a ");
			   sql.append(" left join  lj_service_img_rel b on a.serviceId = b.serviceId where b.type = '1'");
			   sql.append(" order by a.createTime desc ");
				SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
				return query.list().size();
	        } catch (Exception e) {
	            log.error("countServiceList fail",e);
	        }
	        return 0;
	}

	public List<Map> queryServiceDetailById(Map<String, Object> param) {
		 try {
			   StringBuffer sql=new StringBuffer();
			   sql.append(" select a.* from lj_service a where 1=1");
			   sql.append(" and a.serviceId = '"+param.get("serviceId")+"'");
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	        } catch (Exception e) {
	            log.error("queryServiceDetailById fail",e);
	        }
		   return null;
	}

	public List<Map> queryPriceByServiceId(Map<String, Object> param) {
		 try {
			   StringBuffer sql=new StringBuffer();
			   sql.append(" select a.* from lj_service_price_rel a where 1=1");
			   sql.append(" and a.serviceId = '"+param.get("serviceId")+"' order by a.sort");
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	        } catch (Exception e) {
	            log.error("queryPriceByServiceId fail",e);
	        }
		   return null;
	}

	public List<Map> queryImgByServiceId(Map<String, Object> param) {
		try {
			   StringBuffer sql=new StringBuffer();
			   sql.append(" select a.* from lj_service_img_rel a where 1=1");
			   sql.append(" and a.serviceId = '"+param.get("serviceId")+"'");
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	        } catch (Exception e) {
	            log.error("queryImgByServiceId fail",e);
	        }
		   return null;
	}

	public List<Map> queryUserSeerviceRel(Map<String, Object> param) {
		try {
			   StringBuffer sql=new StringBuffer();
			   sql.append(" select a.* from lj_service_user_rel a where 1=1");
			   sql.append(" and a.userId = '"+param.get("userId")+"'");
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	        } catch (Exception e) {
	            log.error("queryUserSeerviceRel fail",e);
	        }
		   return null;
	}

	public void insertUserServiceOrderRel(Service_User_Rel sur) {
			  try {
				entityManager.persist(sur);
			} catch (Exception e) {
				e.printStackTrace();
			}
	}

	public long countPackageList(Map<String, Object> param) {
		try {
			 StringBuffer sql=new StringBuffer();
			   sql.append("select g.serviceName,g.serviceType,e.*,d.remain,t3.done,d.goodStar,f.imgUrl,h.nickName from lj_service_order_user_rel e");
			   sql.append(" left join(select count( t2.orderId) done,t2.orderId	from");
			   sql.append(" (SELECT * FROM lj_service_order_user_rel)t1 left join lj_service_order_detail t2 on t1.orderId = t2.orderId)t3");
				sql.append(" on e.orderId = t3.orderId left join");
			   sql.append(" (select count(*) remain,c.orderId,b.goodStar from (SELECT * FROM lj_service_order_user_rel)a left join lj_service_order_detail c on c.orderId = a.orderId left join");
			   sql.append(" lj_service_judge_rel b on b.orderId = a.orderId");
			   sql.append(" where c.status = '3' GROUP BY c.orderId,b.goodStar)d  on e.orderId = d.orderId left join lj_service_img_rel f on ");
			   sql.append(" e.serviceId= f.serviceId  left join lj_service g on g.serviceId = e.serviceId left join lj_user h on e.userId = h.userId ");
			   sql.append(" where 1=1 and f.type = '1'");
			   if(!StringUtils.isEmpty(param.get("userType"))) {
				   sql.append(" and e.type = '"+param.get("userType")+"'");
			   }
			   if(!StringUtils.isEmpty(param.get("serviceType"))) {
				   sql.append(" and g.serviceType = '"+param.get("serviceType")+"'");
			   }
			   if(!StringUtils.isEmpty(param.get("chargesType"))) {
				   sql.append(" and g.chargesType = '"+param.get("chargesType")+"'");
			   }
			   if((!StringUtils.isEmpty(param.get("phone"))) || (!StringUtils.isEmpty(param.get("name")))) {
				   sql.append(" and (e.phone = '"+param.get("phone")+"'");
				   sql.append(" or h.nickName like '%"+param.get("name")+"%')");
			   }if(!StringUtils.isEmpty(param.get("status"))) {
				   sql.append(" and e.status like '"+param.get("status")+"'");
			   }
			   sql.append("  order by e.createTime desc ");
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
				return query.list().size();
	        } catch (Exception e) {
	            log.error("countServiceList fail",e);
	        }
	        return 0;
	}

	public List<Map> queryPackageList(Map<String, Object> param) {
		 int offset = Integer.parseInt(param.get("offset").toString());
		 int limit = Integer.parseInt(param.get("limit").toString());
		 try {
			   StringBuffer sql=new StringBuffer();
			   sql.append("select g.serviceName,g.serviceType,e.*,d.remain,t3.done,d.goodStar,f.imgUrl,h.nickName from lj_service_order_user_rel e");
			   sql.append(" left join(select count( t2.orderId) done,t2.orderId	from");
			   sql.append(" (SELECT * FROM lj_service_order_user_rel)t1 left join lj_service_order_detail t2 on t1.orderId = t2.orderId)t3");
				sql.append(" on e.orderId = t3.orderId left join");
			   sql.append(" (select count(*) remain,c.orderId,b.goodStar from (SELECT * FROM lj_service_order_user_rel)a left join lj_service_order_detail c on c.orderId = a.orderId left join");
			   sql.append(" lj_service_judge_rel b on b.orderId = a.orderId");
			   sql.append(" where c.status = '3' GROUP BY c.orderId,b.goodStar)d  on e.orderId = d.orderId left join lj_service_img_rel f on ");
			   sql.append(" e.serviceId= f.serviceId  left join lj_service g on g.serviceId = e.serviceId left join lj_user h on e.userId = h.userId ");
			   sql.append(" where 1=1 and f.type = '1'");
			   if(!StringUtils.isEmpty(param.get("userType"))) {
				   sql.append(" and e.type = '"+param.get("userType")+"'");
			   }
			   if(!StringUtils.isEmpty(param.get("userId"))) {
				   sql.append(" and h.userId = '"+param.get("userId")+"'");
			   }
			   if(!StringUtils.isEmpty(param.get("serviceType"))) {
				   sql.append(" and g.serviceType = '"+param.get("serviceType")+"'");
			   }
			   if(!StringUtils.isEmpty(param.get("chargesType"))) {
				   sql.append(" and g.chargesType = '"+param.get("chargesType")+"'");
			   }
			   
			   if((!StringUtils.isEmpty(param.get("phone"))) || (!StringUtils.isEmpty(param.get("name")))) {
				   sql.append(" and (e.phone = '"+param.get("phone")+"'");
				   sql.append(" or h.nickName like '%"+param.get("name")+"%')");
			   }if(!StringUtils.isEmpty(param.get("status"))) {
				   sql.append(" and e.status like '"+param.get("status")+"'");
			   }
			   sql.append("  order by e.createTime desc ");
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setFirstResult(offset)
				        .setMaxResults(limit).list();
	        } catch (Exception e) {
	            log.error("queryServiceList fail",e);
	        }
		   return null;
	}

	public List<Map> queryAreaByCity(Map<String, Object> param) {
		 try {
			   StringBuffer sql=new StringBuffer();
			   sql.append("select * from lj_city_area where 1=1");
			   if(!StringUtils.isEmpty(param.get("cityName"))) {
				   sql.append(" and cityName like '%"+param.get("cityName")+"%'");
			   }
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	        } catch (Exception e) {
	            log.error("queryAreaByCity fail",e);
	        }
		   return null;
	}

	public List<Map> queryCommunityByArea(Map<String, Object> param) {
		try {
			   StringBuffer sql=new StringBuffer();
			   sql.append("select * from lj_area_community where 1=1");
			   if(!StringUtils.isEmpty(param.get("areaName"))) {
				   sql.append(" and area_name like '%"+param.get("areaName")+"%'");
			   }
			   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
			   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	        } catch (Exception e) {
	            log.error("queryAreaByCity fail",e);
	        }
		   return null;
	}

	public void insertServiceOrderDetail(Service_Order_Detail sod) {
		try {
			entityManager.persist(sod);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<Map> queryPackageDetail(Map<String, Object> param) {
		StringBuffer sql=new StringBuffer();
		   sql.append("select a.*,b.nickName,c.serviceName,c.serviceDesc,d.imgUrl from (select * from lj_service_order_user_rel  where orderId = '"+param.get("orderId")+"')a  left join lj_user b on a.userId = b.userId" + 
		   		"	left join lj_service c on c.serviceId = a.serviceId LEFT JOIN lj_service_img_rel d ON c.serviceId = d.serviceId where d.type = '1'");
		   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
		   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	public List<Map> queryOrderDetail(Map<String, Object> param) {
		StringBuffer sql=new StringBuffer();
		   sql.append("select * from lj_service_order_detail where orderId = '"+param.get("orderId")+"'");
		   SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
		   return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	public void insertAppointTime(Map<String, Object> map) {
		StringBuffer sql=new StringBuffer();
		sql.append(" insert into lj_service_order_detail ");
		sql.append(" (orderDetailId,orderId,status,start_serviceDate) ");
		sql.append(" values('"+map.get("id")+"','"+map.get("orderId")+"','"+map.get("status")+"','"+map.get("start_serviceDate")+"')");
		this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString()).executeUpdate();
	}

	public void insertCollect(Map<String, Object> map) {
		try {
			StringBuffer sql=new StringBuffer();
			Collect c=new Collect();
			c.setAppointmentId(map.get("id").toString());
			c.setCreateTime(new Date());
			c.setHouseId(map.get("houseId").toString());
			c.setUserId(map.get("userId").toString());
			entityManager.persist(c);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deleteCollect(Map<String, Object> map) {
		StringBuffer sql=new StringBuffer();
		sql.append(" delete  Collect where 1=1 ");
		sql.append(" and userId = '"+map.get("userId")+"' and houseId = '"+map.get("houseId")+"'");
		Query query =entityManager.createQuery(sql.toString());
		query.executeUpdate();
	}

	public void updateStatus(Service_User_Rel usr){
		try {
			entityManager.merge(usr);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Service_User_Rel getServiceUserRel(Map<String, Object> map){
		try {
			return entityManager.find(Service_User_Rel.class, map.get("orderId").toString());
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public Service_User_Rel getServiceDetail(String orderId){
		try {
			return entityManager.find(Service_User_Rel.class, orderId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
